# -*- coding:utf-8 -*-
import time
from typing import List

import pywencai

from infrastructure.spider.xueqiu_spider import kline
from infrastructure.utils.dbUtil import SqliteUtil, SqliteConfig
from infrastructure.utils.enums import Period, StockTradeDataName
from infrastructure.utils.dateUtil import getTodayyyyymmddInt, differenceFromNow_Days
from config.baseConfiguration import Sqlite3Config
from infrastructure.utils.result import R
from infrastructure.utils.log import logger

config = SqliteConfig(dbPath=Sqlite3Config.dbPath)


def insertHistoryUpBoards(qdt, dti):
    logger.info(f"开始下载{dti}的涨停板数据")
    res = pywencai.get(query=f'{qdt}涨停换手涨停价;涨停原因类别;首次涨停时间;最终涨停时间;涨停开板次数;涨停封单额;连续涨停数;涨停类型;几天几板;涨跌幅;流通市值;行业;概念;省份;城市;非st非新股非退市股', sort_key='股票代码', sort_order='asc', loop=True)

    groupsSql = "select id, name, type from group_info"
    db = SqliteUtil.get_instance(dbConfig=config)
    groups = db.get_all(groupsSql)
    groupDict = {'gn': {g[1]: g for g in groups if g[2]==0}, 'hy': {g[1]: g for g in groups if g[2]==1},
                 'qy': {g[1]: g for g in groups if g[2]==2}, 'id2b': {g[0]: g for g in groups}}
    maxIdSql = "select max(id) from group_info"

    siSql = "select code, name from stock_info"
    siR = db.get_all(siSql)
    sis = {r[0]: r for r in siR}

    l = []
    lCodes = []
    llg = []
    lli = []
    for index, row in res.iterrows():
        codeArr = row['股票代码'].split('.')
        code = codeArr[1] + codeArr[0]
        if code in lCodes:
            continue
        if code not in sis:
            name = row['股票简称']
            db.save(f"insert into stock_info(code, name) VALUES ('{code}', '{name}')")
            sis[code] = (code, name)
        fde = float(row[f"涨停封单额[{dti}]"])
        sz = float(row[f"a股市值(不含限售股)[{dti}]"])
        hs = float(row[f"换手率[{dti}]"])
        cje = round(sz * hs / 100, 2)
        fcb = round(fde / cje * 100, 2)
        zf = float(row[f"涨跌幅:前复权[{dti}]"])
        lbs = row[f"连续涨停天数[{dti}]"]
        ztlx = row[f"涨停类型[{dti}]"]
        ztlx = ztlx if ztlx is not None else ''
        jtjb = row[f"几天几板[{dti}]"]
        jtjb = jtjb if jtjb is not None else ''
        ztj = row[f"涨停价[{dti}]"]
        kbcs = row[f"涨停开板次数[{dti}]"]
        scfb = row[f"首次涨停时间[{dti}]"].strip()
        zzfb = row[f"最终涨停时间[{dti}]"].strip()
        l.append(str((dti, code, 1, zf, ztj, lbs, jtjb, scfb, zzfb, kbcs, fde, sz, ztlx, fcb, cje, hs)))

        ztyyR = row[f"涨停原因类别[{dti}]"]
        ztyys = None
        if ztyyR is not None and len(ztyyR) > 0:
            ztyys = ztyyR.split('+')
        hys = row["所属同花顺行业"].split('-')
        gns = row["所属概念"].split(';') if row["所属概念"] is not None else ""
        sf = row["省份"]
        cs = row["城市"]

        sgR = db.get_all(f"select sg.group_id, sg.code, sg.status, sg.in_dti, sg.out_dti, gi.name, gi.type from stock_group sg left join main.group_info gi on sg.group_id=gi.id where code = '{code}'")
        sgDict = {'gn': {}, 'hy': {}, 'qy': {}, 'id2b': {}}
        toInvalIds = []
        if sgR is not None and len(sgR) > 0:
            sgDict = {'gn': {g[5]: g for g in sgR if g[6] == 0}, 'hy': {g[5]: g for g in sgR if g[6] == 1},
                         'qy': {g[5]: g for g in sgR if g[6] == 2}, 'id2b': {g[0]: g for g in sgR}}
            toInvalIds = [g[0] for g in sgR]

        for hy in hys:
            if hy not in groupDict['hy'].keys():
                groupId = db.get_one(maxIdSql)[0] + 1
                db.save(f"insert into group_info(id, name, type) VALUES ({groupId}, '{hy}', 1)")
                db.save(f"insert into stock_group(group_id, code, status, in_dti) VALUES ({groupId}, '{code}', 1, {dti})")
                groupDict['hy'][hy] = (groupId, hy, 1)
                groupDict['id2b'][groupId] = groupDict['hy'][hy]
            else:
                if hy not in sgDict['hy'].keys():
                    groupId = groupDict['hy'][hy][0]
                    try:
                        db.save(
                            f"insert into stock_group(group_id, code, status, in_dti) VALUES ({groupId}, '{code}', 1, {dti})")
                    except Exception as e:
                        print(e)
                    sgDict['hy'][hy] = (groupId, hy, 1)
                elif hy in sgDict['hy'].keys() and sgDict['hy'][hy][2] == 0:
                    groupId = groupDict['hy'][hy][0]
                    db.save(f"update stock_group set status=1, in_dti={dti} where code='{code}' and group_id={groupId}")
                if hy in sgDict['hy'].keys():
                    groupId = groupDict['hy'][hy][0]
                    if groupId in toInvalIds:
                        toInvalIds.remove(groupId)
        for gn in gns:
            if gn not in groupDict['gn'].keys():
                groupId = db.get_one(maxIdSql)[0] + 1
                db.save(f"insert into group_info(id, name, type) VALUES ({groupId}, '{gn}', 0)")
                db.save(
                    f"insert into stock_group(group_id, code, status, in_dti) VALUES ({groupId}, '{code}', 1, {dti})")
                groupDict['gn'][gn] = (groupId, gn, 0)
                groupDict['id2b'][groupId] = groupDict['gn'][gn]
            else:
                if gn not in sgDict['gn'].keys():
                    groupId = groupDict['gn'][gn][0]
                    try:
                        db.save(
                            f"insert into stock_group(group_id, code, status, in_dti) VALUES ({groupId}, '{code}', 1, {dti})")
                    except Exception as e:
                        logger.error(e)
                    sgDict['gn'][gn] = (groupId, gn, 1)
                elif gn in sgDict['gn'].keys() and sgDict['gn'][gn][2] == 0:
                    groupId = groupDict['gn'][gn][0]
                    db.save(f"update stock_group set status=1, in_dti={dti} where code='{code}' and group_id={groupId}")
                if gn in sgDict['gn'].keys():
                    groupId = groupDict['gn'][gn][0]
                    if groupId in toInvalIds:
                        toInvalIds.remove(groupId)
        if sf not in groupDict['qy'].keys():
            groupId = db.get_one(maxIdSql)[0] + 1
            try:
                db.save(f"insert into group_info(id, name, type) VALUES ({groupId}, '{sf}', 2)")
                db.save(f"insert into stock_group(group_id, code, status, in_dti) VALUES ({groupId}, '{code}', 1, {dti})")
            except Exception as e:
                logger.error(e)
            groupDict['qy'][sf] = (groupId, sf, 2)
            groupDict['id2b'][groupId] = groupDict['qy'][sf]
        else:
            if sf not in sgDict['qy'].keys():
                groupId = groupDict['qy'][sf][0]
                try:
                    db.save(
                        f"insert into stock_group(group_id, code, status, in_dti) VALUES ({groupId}, '{code}', 1, {dti})")
                except Exception as e:
                    logger.error(e)
                sgDict['qy'][sf] = (groupId, sf, 1)
            elif sf in sgDict['qy'].keys() and sgDict['qy'][sf][2] == 0:
                groupId = groupDict['qy'][sf][0]
                db.save(f"update stock_group set status=1, in_dti={dti} where code='{code}' and group_id={groupId}")
            if sf in sgDict['qy'].keys():
                groupId = groupDict['qy'][sf][0]
                if groupId in toInvalIds:
                    toInvalIds.remove(groupId)
        if cs not in groupDict['qy'].keys():
            groupId = db.get_one(maxIdSql)[0] + 1
            db.save(f"insert into group_info(id, name, type) VALUES ({groupId}, '{cs}', 2)")
            db.save(f"insert into stock_group(group_id, code, status, in_dti) VALUES ({groupId}, '{code}', 1, {dti})")
            groupDict['qy'][cs] = (groupId, cs, 2)
            groupDict['id2b'][groupId] = groupDict['qy'][cs]
        else:
            if cs not in sgDict['qy'].keys():
                groupId = groupDict['qy'][cs][0]
                try:
                    db.save(
                        f"insert into stock_group(group_id, code, status, in_dti) VALUES ({groupId}, '{code}', 1, {dti})")
                except Exception as e:
                    logger.error(e)
                sgDict['qy'][cs] = (groupId, cs, 1)
            elif cs in sgDict['qy'].keys() and sgDict['qy'][cs][2] == 0:
                groupId = groupDict['qy'][cs][0]
                db.save(f"update stock_group set status=1, in_dti={dti} where code='{code}' and group_id={groupId}")
            if cs in sgDict['qy'].keys():
                groupId = groupDict['qy'][cs][0]
                if groupId in toInvalIds:
                    toInvalIds.remove(groupId)
        if len(toInvalIds) > 0:
            db.update(f"update stock_group set status=0, out_dti={dti} where group_id in {'('+','.join(tuple([str(i) for i in toInvalIds]))+')'} and code='{code}'")
        if ztyys is not None and len(ztyys) > 0:
            for ztyy in ztyys:
                if ztyy in groupDict['gn'].keys():
                    llg.append(str((dti, code, 0, groupDict['gn'][ztyy][0])))
                elif ztyy in groupDict['hy'].keys():
                    llg.append(str((dti, code, 0, groupDict['hy'][ztyy][0])))
                elif ztyy in groupDict['qy'].keys():
                    llg.append(str((dti, code, 0, groupDict['qy'][ztyy][0])))
                else:
                    lli.append(str((dti, code, 1, ztyy)))

    busql = "insert into stock_board(dti, code, type, rise, price, count, jtjb, first_time, last_time, open_count, board_turnover, market_capitalization, board_state, fcb, cje, turnoverr) VALUES " + ",".join(l)
    db.save(busql)
    if llg is not None and len(llg) > 0:
        blgsql = "insert into stock_board_logic(dti, code, type, group_id) VALUES " + ",".join(llg)
        db.save(blgsql)
    if lli is not None and len(lli) > 0:
        blisql = "insert into stock_board_logic(dti, code, type, info) VALUES " + ",".join(lli)
        db.save(blisql)
    logger.info(f"结束下载{dti}的涨停板数据")

def insertHistoryDownBoards(qdt, dti):
    logger.info(f"开始下载{dti}的跌停板数据")
    query=f'{qdt}跌停换手跌停价;跌停原因类别;首次跌停时间;最终跌停时间;跌停开板次数;跌停封单额;连续跌停数;跌停类型;几天几板;涨跌幅;流通市值;行业;概念;省份;城市;非st非新股非退市股'
    res = pywencai.get(query=query, sort_key='股票代码', sort_order='asc', loop=True)

    groupsSql = "select id, name, type from group_info"
    db = SqliteUtil.get_instance(dbConfig=config)
    groups = db.get_all(groupsSql)
    groupDict = {'gn': {g[1]: g for g in groups if g[2] == 0}, 'hy': {g[1]: g for g in groups if g[2] == 1},
                 'qy': {g[1]: g for g in groups if g[2] == 2}, 'id2b': {g[0]: g for g in groups}}
    maxIdSql = "select max(id) from group_info"

    siSql = "select code, name from stock_info"
    siR = db.get_all(siSql)
    sis = {r[0]: r for r in siR}

    l = []
    llg = []
    lli = []
    if res is None or res.empty:
        res = pywencai.get(query=query, sort_key='股票代码', sort_order='asc', loop=True)
    if res is None or res.empty:
        logger.info(f"没有{dti}的跌停板数据, 结束下载")
        return
    for index, row in res.iterrows():
        codeArr = row['股票代码'].split('.')
        code = codeArr[1] + codeArr[0]
        if code not in sis:
            name = row['股票简称']
            db.save(f"insert into stock_info(code, name) VALUES ('{code}', '{name}')")
        fde = float(row[f"跌停封单额[{dti}]"])
        sz = float(row[f"a股市值(不含限售股)[{dti}]"])
        hs = float(row[f"换手率[{dti}]"])
        cje = round(sz * hs / 100, 2)
        fcb = round(fde / cje * 100, 2)

        zf = float(row[f"涨跌幅:前复权[{dti}]"])
        lbs = row[f"连续跌停天数[{dti}]"]
        dtlx = row[f"跌停类型[{dti}]"]
        if dtlx is None:
            dtlx = ''
        dtyy = row[f"跌停原因类型[{dti}]"]
        dtyy = dtyy if dtyy is not None else ""
        jtjb = row[f"几天几板[{dti}]"]
        jtjb = jtjb if jtjb is not None else ""
        dtj = row[f"跌停价[{dti}]"]
        kbcs = row[f"跌停开板次数[{dti}]"]
        scfb = row[f"首次跌停时间[{dti}]"].strip()
        zzfb = row[f"最终跌停时间[{dti}]"].strip()
        l.append(str((dti, code, 0, zf, dtj, lbs, jtjb, scfb, zzfb, kbcs, fde, sz, dtlx, fcb, cje, hs)))

        dtyyR = row[f"跌停原因类型[{dti}]"]
        dtyys = None
        if dtyyR is not None and len(dtyyR) > 0:
            dtyys = dtyyR.split('+')
        hys = row["所属同花顺行业"].split('-')
        gns = row["所属概念"].split(';')
        sf = row["省份"]
        cs = row["城市"]

        sgR = db.get_all(f"select sg.group_id, sg.code, sg.status, sg.in_dti, sg.out_dti, gi.name, gi.type from stock_group sg left join main.group_info gi on sg.group_id=gi.id where code = '{code}'")
        sgDict = {'gn': {}, 'hy': {}, 'qy': {}, 'id2b': {}}
        inIds = []
        if sgR is not None and len(sgR) > 0:
            sgDict = {'gn': {g[5]: g for g in sgR if g[6] == 0}, 'hy': {g[5]: g for g in sgR if g[6] == 1},
                         'qy': {g[5]: g for g in sgR if g[6] == 2}, 'id2b': {g[0]: g for g in sgR}}
            inIds = [g[0] for g in sgR]

        for hy in hys:
            if hy not in groupDict['hy'].keys():
                groupId = db.get_one(maxIdSql)[0] + 1
                db.save(f"insert into group_info(id, name, type) VALUES ({groupId}, '{hy}', 1)")
                db.save(f"insert into stock_group(group_id, code, status, in_dti) VALUES ({groupId}, '{code}', 1, {dti})")
                groupDict['hy'][hy] = (groupId, hy, 1)
                groupDict['id2b'][groupId] = groupDict['hy'][hy]
            else:
                if hy not in sgDict['hy'].keys():
                    groupId = groupDict['hy'][hy][0]
                    try:
                        db.save(
                            f"insert into stock_group(group_id, code, status, in_dti) VALUES ({groupId}, '{code}', 1, {dti})")
                    except Exception as e:
                        logger.error(e)
                elif hy in sgDict['hy'].keys() and sgDict['hy'][hy][2] == 0:
                    groupId = groupDict['hy'][hy][0]
                    db.save(f"update stock_group set status=1, in_dti={dti} where code='{code}' and group_id={groupId}")
                if hy in sgDict['hy'].keys():
                    groupId = groupDict['hy'][hy][0]
                    if groupId in inIds:
                        inIds.remove(groupId)
        for gn in gns:
            if gn not in groupDict['gn'].keys():
                groupId = db.get_one(maxIdSql)[0] + 1
                db.save(f"insert into group_info(id, name, type) VALUES ({groupId}, '{gn}', 0)")
                db.save(
                    f"insert into stock_group(group_id, code, status, in_dti) VALUES ({groupId}, '{code}', 1, {dti})")
                groupDict['gn'][gn] = (groupId, gn, 0)
                groupDict['id2b'][groupId] = groupDict['gn'][gn]
            else:
                if gn not in sgDict['gn'].keys():
                    groupId = groupDict['gn'][gn][0]
                    try:
                        db.save(
                            f"insert into stock_group(group_id, code, status, in_dti) VALUES ({groupId}, '{code}', 1, {dti})")
                    except Exception as e:
                        logger.error(e)
                elif gn in sgDict['gn'].keys() and sgDict['gn'][gn][2] == 0:
                    groupId = groupDict['gn'][gn][0]
                    db.save(f"update stock_group set status=1, in_dti={dti} where code='{code}' and group_id={groupId}")
                if gn in sgDict['gn'].keys():
                    groupId = groupDict['gn'][gn][0]
                    if groupId in inIds:
                        inIds.remove(groupId)
        if sf not in groupDict['qy'].keys():
            groupId = db.get_one(maxIdSql)[0] + 1
            db.save(f"insert into group_info(id, name, type) VALUES ({groupId}, '{sf}', 2)")
            db.save(f"insert into stock_group(group_id, code, status, in_dti) VALUES ({groupId}, '{code}', 1, {dti})")
            groupDict['qy'][sf] = (groupId, sf, 2)
            groupDict['id2b'][groupId] = groupDict['qy'][sf]
        else:
            if sf not in sgDict['qy'].keys():
                groupId = groupDict['qy'][sf][0]
                try:
                    db.save(
                        f"insert into stock_group(group_id, code, status, in_dti) VALUES ({groupId}, '{code}', 1, {dti})")
                except Exception as e:
                    logger.error(e)
            elif sf in sgDict['qy'].keys() and sgDict['qy'][sf][2] == 0:
                groupId = groupDict['qy'][sf][0]
                db.save(f"update stock_group set status=1, in_dti={dti} where code='{code}' and group_id={groupId}")
            if sf in sgDict['qy'].keys():
                groupId = groupDict['qy'][sf][0]
                inIds.remove(groupId)
        if cs not in groupDict['qy'].keys():
            groupId = db.get_one(maxIdSql)[0] + 1
            db.save(f"insert into group_info(id, name, type) VALUES ({groupId}, '{cs}', 2)")
            db.save(f"insert into stock_group(group_id, code, status, in_dti) VALUES ({groupId}, '{code}', 1, {dti})")
            groupDict['qy'][cs] = (groupId, cs, 2)
            groupDict['id2b'][groupId] = groupDict['qy'][cs]
        else:
            if cs not in sgDict['qy'].keys():
                groupId = groupDict['qy'][cs][0]
                try:
                    db.save(
                        f"insert into stock_group(group_id, code, status, in_dti) VALUES ({groupId}, '{code}', 1, {dti})")
                except Exception as e:
                    logger.error(e)
            elif cs in sgDict['qy'].keys() and sgDict['qy'][cs][2] == 0:
                groupId = groupDict['qy'][cs][0]
                db.save(f"update stock_group set status=1, in_dti={dti} where code='{code}' and group_id={groupId}")
            if cs in sgDict['qy'].keys():
                groupId = groupDict['qy'][cs][0]
                if groupId in inIds:
                    inIds.remove(groupId)
        if len(inIds) > 0:
            for inId in inIds:
                db.update(f"update stock_group set status=0, out_dti={dti} where group_id={inId} and code='{code}'")
        if dtyys is not None and len(dtyys) > 0:
            for dtyy in dtyys:
                if dtyy in groupDict['gn'].keys():
                    llg.append(str((dti, code, 0, groupDict['gn'][dtyy][0])))
                elif dtyy in groupDict['hy'].keys():
                    llg.append(str((dti, code, 0, groupDict['hy'][dtyy][0])))
                elif dtyy in groupDict['qy'].keys():
                    llg.append(str((dti, code, 0, groupDict['qy'][dtyy][0])))
                else:
                    lli.append(str((dti, code, 1, dtyy)))

    busql = """
        insert into stock_board(dti, code, type, rise, price, count, jtjb, 
            first_time, last_time, open_count, board_turnover, market_capitalization, 
            board_state, fcb, cje, turnoverr) VALUES """ + ",".join(l)
    db.save(busql)
    if llg is not None and len(llg) > 0:
        blgsql = "insert into stock_board_logic(dti, code, type, group_id) VALUES " + ",".join(llg)
        db.save(blgsql)
    if lli is not None and len(lli) > 0:
        blisql = "insert into stock_board_logic(dti, code, type, info) VALUES " + ",".join(lli)
        db.save(blisql)
    logger.info(f"结束下载{dti}的跌停板数据")

def updateHistoryHs(dti, qdt):
    db = SqliteUtil.get_instance(dbConfig=config)
    res = pywencai.get(query=f'{qdt}涨停换手涨停价非st非新股非退市股', sort_key='股票代码', sort_order='asc', loop=True)
    if res is not None and not res.empty:
        for index, row in res.iterrows():
            nameArr = row['股票代码'].split('.')
            code = nameArr[1] + nameArr[0]
            fde = float(row[f"涨停封单额[{dti}]"])
            sz = float(row[f"a股市值(不含限售股)[{dti}]"])
            hs = float(row[f"换手率[{dti}]"])
            cje = round(sz*hs/100, 2)
            fcb = round(fde / cje * 100, 2)
            zf = float(row[f"涨跌幅:前复权[{dti}]"])
            lbs = row[f"连续涨停天数[{dti}]"]
            ztlx = row[f"涨停类型[{dti}]"]
            jtjb = row[f"几天几板[{dti}]"]
            ztj = row[f"涨停价[{dti}]"]
            kbcs = row[f"涨停开板次数[{dti}]"]
            scfb = row[f"首次涨停时间[{dti}]"]
            zzfb = row[f"最终涨停时间[{dti}]"]
            sql = f"""
                update stock_board set price={ztj}, count={lbs}, jtjb={jtjb}, rise={zf},
                    first_time='{scfb}', last_time='{zzfb}', open_count={kbcs},
                    board_turnover={fde}, market_capitalization={sz}, board_state='{ztlx}',
                    fcb={fcb}, cje={cje}, turnoverr={hs}
                where dti={dti} and code='{code}'
                """
            db.update(sql)
    time.sleep(2)
    res = pywencai.get(query=f'{qdt}跌停换手跌停价非st非新股非退市股', sort_key='股票代码', sort_order='asc', loop=True)
    if res is not None and not res.empty:
        for index, row in res.iterrows():
            nameArr = row['股票代码'].split('.')
            code = nameArr[1] + nameArr[0]
            fde = float(row[f"跌停封单额[{dti}]"])
            fcb = float(row[f"跌停封单量占成交量比[{dti}]"])
            fszb = float(row[f"跌停封单量占流通a股比[{dti}]"])
            sz = round(fde/fszb * 100, 2)
            hs = float(row[f"换手率[{dti}]"])
            cje = float(row[f"成交额[{dti}]"])

            zf = float(row[f"涨跌幅:前复权[{dti}]"])
            lbs = row[f"连续跌停天数[{dti}]"]
            dtlx = row[f"跌停类型[{dti}]"]
            dtj = row[f"跌停价[{dti}]"]
            kbcs = row[f"跌停开板次数[{dti}]"]
            scfb = row[f"首次跌停时间[{dti}]"]
            zzfb = row[f"最终跌停时间[{dti}]"]
            sql = f"""
                update stock_board set price={dtj}, count={lbs}, rise={zf}, 
                    first_time='{scfb}', last_time='{zzfb}', open_count={kbcs},
                    board_turnover={fde}, market_capitalization={sz}, board_state='{dtlx}',
                    fcb={fcb}, cje={cje}, turnoverr={hs}
                where dti={dti} and code='{code}'
                """
            db = SqliteUtil.get_instance(dbConfig=config)
            db.update(sql)


def updateHistoryTOpen(dti, tdti, qdt, tqdt):
    res = pywencai.get(query=f'{qdt}涨停{tqdt}开盘价非st非新股', sort_key='股票代码', sort_order='asc', loop=True)
    db = SqliteUtil.get_instance(dbConfig=config)
    if res is not None and not res.empty:
        for index, row in res.iterrows():
            nameArr = row['股票代码'].split('.')
            code = nameArr[1] + nameArr[0]
            ssql = f"select dti, code, price from stock_board where code='{code}' and dti={dti}"
            one = db.get_one(ssql)
            if one is None:
                continue
            price = one[2]
            topen = float(row[f"开盘价:不复权[{tdti}]"])
            thigh = float(row[f"最高价:不复权[{tdti}]"])
            tlow = float(row[f"最低价:不复权[{tdti}]"])
            tclose = float(row[f"收盘价:不复权[{tdti}]"])
            topenzf = round((topen - price)/price * 100, 2)
            tclosezf = round((tclose - price)/price * 100, 2)
            thighzf = round((thigh - price)/price * 100, 2)
            tlowzf = round((tlow - price)/price * 100, 2)
            sql = f"""
                update stock_board 
                set topen={topen}, thigh={thigh}, tlow={tlow}, tclose={tclose}, 
                    topenzf={topenzf}, tclosezf={tclosezf}, thighzf={thighzf}, tlowzf={tlowzf}
                where dti={dti} and code='{code}'
                """
            db.update(sql)
    time.sleep(2)
    res = pywencai.get(query=f'{qdt}跌停{tqdt}开盘价非st非新股', sort_key='股票代码', sort_order='asc', loop=True)
    if res is not None and not res.empty:
        for index, row in res.iterrows():
            nameArr = row['股票代码'].split('.')
            code = nameArr[1] + nameArr[0]
            ssql = f"select dti, code, price from stock_board where code='{code}' and dti={dti}"
            one = db.get_one(ssql)
            if one is None:
                continue
            price = one[2]
            topen = float(row[f"开盘价:不复权[{tdti}]"])
            thigh = float(row[f"最高价:不复权[{tdti}]"])
            tlow = float(row[f"最低价:不复权[{tdti}]"])
            tclose = float(row[f"收盘价:不复权[{tdti}]"])
            topenzf = round((topen - price)/price * 100, 2)
            tclosezf = round((tclose - price)/price * 100, 2)
            thighzf = round((thigh - price) / price * 100, 2)
            tlowzf = round((tlow - price) / price * 100, 2)
            sql = f"""
                update stock_board 
                set topen={topen}, thigh={thigh}, tlow={tlow}, tclose={tclose}, 
                    topenzf={topenzf}, tclosezf={tclosezf}, thighzf={thighzf}, tlowzf={tlowzf}
                where dti={dti} and code='{code}'
                """
            db.update(sql)


def persistenceStockMove(dti, stockCodes: List[str]=None):
    logger.info(f"开始下载{dti}的股票运行数据")
    dt = str(dti)
    dt = dt[0:4] + '-' + dt[4:6] + '-' + dt[6:]
    db = SqliteUtil.get_instance(dbConfig=config)
    preDtiSql = f"select dti from market_sentiment where dti < {dti} order by dti desc limit 1"
    preDtiR = db.get_one(preDtiSql)
    if preDtiR is None:
        return R.error(msg=f"没有找到上一个记录的交易日期,当天股票交易日期为{dti}")
    preDti = preDtiR[0]
    preDt = str(preDti)
    preDt = preDt[0:4] + '-' + preDt[4:6] + '-' + preDt[6:]
    dtc = differenceFromNow_Days(preDti)
    if stockCodes is None or len(stockCodes) == 0:
        db.update(f"update stock_board set tdti={dti} where dti={preDti}")
        stockCodes = []
        sql = f"select code from stock_board where dti={preDti}"
        res = db.get_all(sql)
        if res is None or len(res) == 0:
            return R.error(msg=f"没有需要记录的数据条目")
        for r in res:
            code = r[0]
            stockCodes.append(code)
    def do(stockCode: str):
        kdfDay = kline(stockCode, period=Period.day, days=dtc)
        preclose = kdfDay.loc[preDt][StockTradeDataName.CLOSE.value]
        try:
            dtkdfDay = kdfDay.loc[dt]
        except Exception as e:
            print(e)
            return
        open = dtkdfDay[StockTradeDataName.OPEN.value]
        close = dtkdfDay[StockTradeDataName.CLOSE.value]
        high = dtkdfDay[StockTradeDataName.HIGH.value]
        low = dtkdfDay[StockTradeDataName.LOW.value]
        zf_close = (close - preclose) / preclose * 100
        zf_open = (open - preclose) / preclose * 100
        zf_high = (high - preclose) / preclose * 100
        zf_low = (low - preclose) / preclose * 100
        vol = dtkdfDay[StockTradeDataName.VOL.value]
        amount = dtkdfDay[StockTradeDataName.AMOUNT.value]

        kdfMin5 = kline(stockCode, period=Period.minute5, days=dtc)
        try:
            dtkdfMin5 = kdfMin5.loc[dt]
            high_time = dtkdfMin5[StockTradeDataName.HIGH.value].idxmax()
            low_time = dtkdfMin5[StockTradeDataName.LOW.value].idxmin()
            smISql = f"""
                        insert into stock_move(dti, code, open, close, high, low, pre_close, zf_open, zf_high, zf_low, zf_close, vol, amount, high_time, low_time)
                        values({dti}, '{stockCode}', {open}, {close}, {high}, {low}, {preclose}, {zf_open}, {zf_high}, {zf_low}, {zf_close}, {vol}, {amount}, '{high_time}', '{low_time}')
                    """
            db.save(smISql)
        except Exception as e:
            smISql = f"""
                insert into stock_move(dti, code, open, close, high, low, pre_close, zf_open, zf_high, zf_low, zf_close, vol, amount)
                values({dti}, '{stockCode}', {open}, {close}, {high}, {low}, {preclose}, {zf_open}, {zf_high}, {zf_low}, {zf_close}, {vol}, {amount})
            """
            db.save(smISql)

    cc = len(stockCodes)
    logger.info(f"需要下载{cc}个股票运行数据")
    for i in range(cc):
        code = stockCodes[i]
        logger.info(f"开始下载第{i+1}/{cc}个股票({code})的运行数据")
        do(code)
    logger.info(f"结束下载{dti}的股票运行数据")
    return R.success()

def updateDTYY(qdt, dti):
    res = pywencai.get(query=f'{qdt}跌停换手跌停价非st非新股非退市股', sort_key='股票代码', sort_order='asc', loop=True)
    for index, row in res.iterrows():
        nameArr = row['股票代码'].split('.')
        code = nameArr[1] + nameArr[0]
        dtyy = row[f"跌停原因类型[{dti}]"]
        usql = f"update stock_board_logic set info='{dtyy}' where code='{code}' and dti={dti}"
        db = SqliteUtil.get_instance(dbConfig=config)
        db.update(usql)


def updatejtjb(qdt, dti):
    res = pywencai.get(query=f'{qdt}涨停非st非新股非退市股', sort_key='股票代码', sort_order='asc', loop=True)
    for index, row in res.iterrows():
        nameArr = row['股票代码'].split('.')
        code = nameArr[1] + nameArr[0]
        jtjb = row[f"几天几板[{dti}]"]
        usql = f"update stock_board set jtjb='{jtjb}' where dti={dti} and code='{code}'"
        db = SqliteUtil.get_instance(dbConfig=config)
        db.update(usql)


def updateHistoryZf(dti):
    db = SqliteUtil.get_instance(dbConfig=config)
    sql = f"select code, price, thigh, tlow from stock_board where dti={dti}"
    all = db.get_all(sql)
    for row in all:
        code = row[0]
        price = float(row[1]) if row[1] is not None else 0
        thigh = float(row[2]) if row[2] is not None else 0
        tlow = float(row[3]) if row[3] is not None else 0
        if price == 0 or thigh == 0 or tlow == 0:
            continue
        thighzf = round((thigh - price)/price * 100, 2)
        tlowzf = round((tlow - price)/price * 100, 2)
        usql = f"update stock_board set thighzf={thighzf}, tlowzf={tlowzf} where code='{code}' and dti={dti}"
        db = SqliteUtil.get_instance(dbConfig=config)
        db.update(usql)


def deleteSB(dti: int = None):
    db = SqliteUtil.get_instance(dbConfig=config)
    if dti is None:
        dti = getTodayyyyymmddInt()
    db = SqliteUtil.get_instance(dbConfig=config)
    db.delete(f"delete from stock_board_logic where dti={dti}")
    logger.info(f"删除{dti}的股票封板逻辑信息成功")
    db.delete(f"delete from stock_board where dti={dti}")
    logger.info(f"删除{dti}的股票封板信息成功")
    db.delete(f"delete from stock_move where dti={dti}")
    logger.info(f"删除{dti}的前一交易日封板股票的{dti}波动信息成功")
    logger.info(f"删除{dti}的封板股票信息成功")


def stockBoard(dti: int = None):
    if dti is None:
        dti = getTodayyyyymmddInt()
    logger.info(f"开始下载{dti}封板数据")
    qdt = f"{str(dti)[4:6]}月{str(dti)[6:]}日"
    insertHistoryUpBoards(qdt, dti)
    insertHistoryDownBoards(qdt, dti)
    persistenceStockMove(dti)
    logger.info(f"结束下载{dti}封板数据")


def historyMaintain(dtis: List[int]) -> R:
    if dtis is None or len(dtis) == 0:
        return R.fail("请先指定需要维护的日期")
    for dti in dtis:
        persistenceStockMove(dti)
    return R.success()




if __name__ == '__main__':
    stockBoard()